﻿/* =========================================================================== 
| Autors: Arnau Solsona, Javier Alonso, Jordi Mateu, Maria Antonia, Xavi Font
| Projecte: eOlympics
| Mòdul: scriptDades.sql
| Descripció: Script de creació de la base de dades PAC4
| 
| Revisions:
| Data		 Nom	         Revisió
| ---------- --------------- ---------------------------------------
| 07-05-2012 Xavi Font		 Creat
| 20-05-2012 Xavi Font		 Última modificació
| 
 ============================================================================== */

-- Definició de les variables globals necessàries
-- per generar massivament les dades de prova de la BBDD

-- Paràmetres usuaris
SELECT put_var('dades_usuari.nom_responsables', 'Responsable',
	'Nom dels responsables d''events');
SELECT put_var('dades_usuari.min_responsables', '1',
	'Nombre mínim de responsables d''events per universitat');
SELECT put_var('dades_usuari.max_responsables', '5',
	'Nombre màxim de responsables d''events per universitat');
SELECT put_var('dades_usuari.nom_estudiants', 'Estudiant',
	'Nom dels estudiants');
SELECT put_var('dades_usuari.min_estudiants', '25',
	'Nombre mínim d''estudiants per universitat');
SELECT put_var('dades_usuari.max_estudiants', '50',
	'Nombre màxim d''estudiants per universitat');

-- Paràmetres proves
SELECT put_var('dades_prova.dies_min_datainiinscripcio', '-365',
	'Inscripció mínima fa un any');
SELECT put_var('dades_prova.dies_max_datainiinscripcio', '60',
	'Inscripció màxima d''aqui a 2 mesos');
SELECT put_var('dades_prova.dies_min_durada_inscripcio', '1',
	'Durada mínima període d''inscripció');
SELECT put_var('dades_prova.dies_max_durada_inscripcio', '60',
	'Durada màxima període d''inscripció');
SELECT put_var('dades_prova.dies_min_espera_realitzacio', '7',
	'Espera mínima per a realitzar la prova');
SELECT put_var('dades_prova.dies_max_espera_realitzacio', '14',
	'Espera màxima per a realitzar la prova');
SELECT put_var('dades_prova.dies_min_durada_realitzacio', '1',
	'Durada mínima per a realitzar la prova');
SELECT put_var('dades_prova.dies_max_durada_realitzacio', '30',
	'Durada màxima per a realitzar la prova');
SELECT put_var('dades_prova.min_total', '100',
	'Mínim quantitat de proves totals');
SELECT put_var('dades_prova.max_total', '200',
	'Màxim quantitat de proves totals');

-- Paràmetres inscripció
SELECT put_var('dades_inscripcio.mitjana', '0.8', -- 0.: buides, 1.: plenes
	'Mitjana d''inscripció per prova (en tant per 1)');
SELECT put_var('dades_inscripcio.variança', '0.2', -- 0.: igual que mitjana, 1.: qualsevol valor
	'Variança d''inscripció per prova (en tant per 1)');

-- Paràmetres participació
SELECT put_var('dades_participacio.probabilitat', '0.9', -- 0.: no participa, 1.: participa
	'Probabilitat de participació per inscripció (en tant per 1)');
SELECT put_var('dades_resposta.prob_encert', '0.5',
	'Probabilitat de respostes encertades per estudiant');
SELECT put_var('dades_resposta.prob_error', '0.4',
	'Probabilitat de respostes incorrectes per estudiant');
SELECT put_var('dades_resposta.prob_enblanc', '0.1',
	'Probabilitat de respostes en blanc per estudiant');

-- Funció que retorna un enter aleatòri d'entre dos enters
CREATE OR REPLACE FUNCTION get_aleatori(integer, integer) RETURNS integer AS $$
  SELECT $1 + CAST(random() * ($2 - $1) AS integer)
$$ LANGUAGE sql;

-- Funció que retorna un enter aleatòri d'entre dos variables globals enteres
CREATE OR REPLACE FUNCTION get_aleatori_int(text, text) RETURNS integer AS $$
  SELECT get_aleatori(get_var_int($1), get_var_int($2))
$$ LANGUAGE sql;

-- Funció que retorna un enter aleatòri d'entre una variables global entera i uns diferencials
CREATE OR REPLACE FUNCTION get_aleatori_var_dif(text, integer, integer) RETURNS integer AS $$
  SELECT get_aleatori(CAST(get_var_int($1)+$2 AS integer), CAST(get_var_int($1)+$3 AS integer))
$$ LANGUAGE sql;

-- Funció que retorna un decimal aleatòri d'entre dos decimals
CREATE OR REPLACE FUNCTION get_aleatori(double precision, double precision) RETURNS double precision AS $$
  SELECT $1 + random() * ($2 - $1)
$$ LANGUAGE sql;

-- Funció que retorna un decimal aleatòri d'entre dos variables globals decimals
CREATE OR REPLACE FUNCTION get_aleatori_double(text, text) RETURNS double precision AS $$
  SELECT get_aleatori(get_var_double($1), get_var_double($2))
$$ LANGUAGE sql;


-- Elimina en cascada els valors de la taula UNIVERSITAT
TRUNCATE universitat CASCADE;

-- Afegeix les universitats a la taula UNIVERSITAT
INSERT INTO universitat (nomuniversitat)
VALUES
('Universitat Oberta de Catalunya (UOC)'),
('Universitat Autònoma de Catalunya (UAB)'),
('Universitat Politècnica de Catalunya (UPC)'),
('Universitat Internacional de Catalunya (UIC)'),
('Universitat Pompeu Fabra (UPF)'),
('Universitat de Barcelona (UB)');

-- Elimina en cascada tots els usuaris de la taula USUARI (això elimina tots els registres de la BBDD)
TRUNCATE usuari CASCADE;

-- Funció que afegeix aleatòriament les dades d'auditori de cada usuari
CREATE OR REPLACE FUNCTION auditoriaUsuari() RETURNS TRIGGER AS $auditoriaUsuari$
BEGIN
	IF NEW.tipususuari = 'A' THEN
		NEW.dataAlta = current_date + get_var_int('dades_prova.dies_min_datainiinscripcio') -30;
		IF NEW.idusuari = 'root' THEN
			NEW.usuariAlta = NEW.idintern;
		ELSE
			NEW.usuariAlta = (SELECT idintern FROM usuari WHERE idusuari = 'root');
		END IF;
	ELSE
		NEW.dataAlta = current_date + get_aleatori_var_dif('dades_prova.dies_min_datainiinscripcio', -30, 0);
		NEW.usuariAlta = (SELECT idintern FROM (SELECT idintern, random() AS ordre FROM usuari
			WHERE idusuari <> 'root' AND tipususuari = 'A') AS u
			ORDER BY u.ordre LIMIT 1);
	END IF;
	NEW.dataMod = NEW.dataAlta;
	NEW.usuariMod = NEW.usuariAlta;
	RETURN NEW;
END;
$auditoriaUsuari$ LANGUAGE plpgsql;

-- Disparador que crida a la funció anterior quan s'afegeix un usuari
CREATE TRIGGER auditoriaUsuari BEFORE INSERT ON usuari
    FOR EACH ROW EXECUTE PROCEDURE auditoriaUsuari();

-- Afegeix tots els usuaris administradors a la taula USUARI
INSERT INTO usuari (tipususuari, idusuari, pwd, email, estatensistema, tipusdocumentid, "document", nom, cognom1, cognom2)
VALUES
('A', 'root', '1234', 'root@uoc.edu', 'A', 'NIF', '12345678A', 'Usuari', 'Administrador', 'UOC'),
('A', 'Arnau', '1234', 'Arnau@uoc.edu', 'A', 'NIF', '12345678A', 'Arnau', 'Garcia', 'López'),
('A', 'Javi', '1234', 'Javi@uoc.edu', 'A', 'NIF', '12345678A', 'Javi', 'Garcia', 'López'),
('A', 'Jordi', '1234', 'Jordi@uoc.edu', 'A', 'NIF', '12345678A', 'Jordi', 'Garcia', 'López'),
('A', 'Maria', '1234', 'Maria@uoc.edu', 'A', 'NIF', '12345678A', 'Maria', 'Garcia', 'López'),
('A', 'Xavi', '1234', 'Xavi@uoc.edu', 'A', 'NIF', '12345678A', 'Xavi', 'Garcia', 'López');

-- Comprova que no existeixi la taula temporal usuari_tmp
DROP TABLE IF EXISTS usuari_tmp;

-- Crea i omple la taula temporal usuari_tmp amb tots els responsables d'events i estudiants de cada universitat
SELECT 	p.tipus AS tipususuari, p.descripcio || u.uni || p.num AS idusuari, '1234' AS pwd,
	lower(p.descripcio || p.num || '@' || u.uni || '.edu') AS email,
	'A' AS estatensistema, 'NIF' AS tipusdocumentid, '12345678A' AS "document",
	p.descripcio || u.uni || p.num AS nom, 'Cognom1' AS cognom1, 'Cognom2' AS cognom2, u.iduniversitat AS iduniversitat
INTO TEMP TABLE usuari_tmp FROM
	-- Responsables d'events per universitat
	(SELECT 'R' AS tipus, get_var('dades_usuari.nom_responsables') AS descripcio, num
		FROM generate_series(get_var_int('dades_usuari.min_responsables'),
			get_aleatori_int('dades_usuari.min_responsables', 'dades_usuari.max_responsables')) AS num
	UNION
	-- Estudiants per universitat
	SELECT 'E' AS tipus, get_var('dades_usuari.nom_estudiants') AS descripcio, num
		FROM generate_series(get_var_int('dades_usuari.min_estudiants'),
			get_aleatori_int('dades_usuari.min_estudiants', 'dades_usuari.max_estudiants')) AS num) AS p,
	(SELECT iduniversitat, substring(nomuniversitat from position('(' in nomuniversitat)+1 for char_length(nomuniversitat) - position('(' in nomuniversitat)-1) AS uni
	FROM universitat) AS u;

-- Afegeix els usuaris de la taula temporal creada anteriorment a la taula USUARI
INSERT INTO usuari (tipususuari, idusuari, pwd, email, estatensistema, tipusdocumentid, "document", nom, cognom1, cognom2)
SELECT tipususuari, idusuari, pwd, email, estatensistema, tipusdocumentid, "document", nom, cognom1, cognom2 FROM usuari_tmp;

-- Afegeix el idIntern dels administradors a la taula ADMINISTRADOR
INSERT INTO administrador (idadministrador)
SELECT idintern FROM usuari WHERE tipususuari = 'A';

-- Afegeix el idIntern dels responsables d'events a la taula RESPONSABLEEVENTS
INSERT INTO responsableevents (idresponsable)
SELECT idintern FROM usuari WHERE tipususuari = 'R';

-- Afegeix el idIntern i el corresponent idUniversitat dels estudiants a la taula ESTUDIANT
INSERT INTO estudiant (idestudiant, iduniversitat)
SELECT u.idintern, tmp.iduniversitat FROM usuari AS u
LEFT JOIN usuari_tmp AS tmp ON tmp. idusuari = u.idusuari
WHERE u.tipususuari = 'E';

-- Elimina la taula temporal usuari_tmp de memòria (tot i que al finalitzar la sessió aquesta ja s'esborra)
DROP TABLE usuari_tmp;

-- Eliminem el disparador i la funció de generació per a que no estiguin operatives en producció
DROP TRIGGER auditoriaUsuari ON usuari;
DROP FUNCTION IF EXISTS auditoriaUsuari();

-- Elimina en cascada totes les proves de la taula PROVA (això elimina totes les rondes i classificacions de la BBDD)
TRUNCATE prova CASCADE;

-- Funció que genera prova de forma aleatòria
CREATE OR REPLACE FUNCTION generarProva() RETURNS TRIGGER AS $generarProva$
DECLARE n integer;
BEGIN
	-- inscripció de un any enrera a el més següent
	NEW.datainiinscripcio := current_date
		+ get_aleatori_int('dades_prova.dies_min_datainiinscripcio', 'dades_prova.dies_max_datainiinscripcio');
	-- temps d'inscripció de 1 dia a 1 mes
	NEW.datafiinscripcio := NEW.datainiinscripcio
		+ get_aleatori_int('dades_prova.dies_min_durada_inscripcio', 'dades_prova.dies_max_durada_inscripcio');
	NEW.nombreplaces := get_aleatori_int('prova.min_participants', 'prova.max_participants');
	-- generem les dades d'auditoria
	NEW.dataAlta = NEW.datainiinscripcio + get_aleatori(-30, -7);
	NEW.usuariAlta = (SELECT idintern FROM (SELECT idintern, random() AS ordre FROM usuari
		WHERE tipususuari = 'R') AS u ORDER BY u.ordre LIMIT 1);
	NEW.dataMod = NEW.dataAlta;
	NEW.usuariMod = NEW.usuariAlta;
	RETURN NEW;
END;
$generarProva$ LANGUAGE plpgsql;

CREATE SEQUENCE seqIncripcio START 1;
-- Funció que genera inscripcions de forma aleatòria
CREATE OR REPLACE FUNCTION generarInscripcio() RETURNS TRIGGER AS $generarInscripcio$
DECLARE
	places integer;
	seq_start integer;
BEGIN
	IF NEW.datainiinscripcio <= current_date THEN
		places := (SELECT nombrePlaces FROM prova WHERE idprova = NEW.idprova);
		seq_start := nextval('seqIncripcio');
		-- Inscriu diversos estudiants a les proves amb inici d'inscripció anterior a la data actual
		INSERT INTO inscripcio (idprova, idestudiant, datainscripcio)
		SELECT NEW.idprova, e.idestudiant, NEW.datainiinscripcio
			+ CAST(random()*(least(current_date, NEW.datafiinscripcio) - NEW.datainiinscripcio) AS integer) AS datainscripcio
		FROM (SELECT idestudiant, nextval('seqIncripcio') AS posicio
			FROM (SELECT idestudiant, random() AS ordre FROM estudiant) AS o
			ORDER BY o.ordre) AS e
		WHERE e.posicio > seq_start AND e.posicio <= seq_start +
			CAST(places * get_aleatori(get_var_double('dades_inscripcio.mitjana')-get_var_double('dades_inscripcio.variança'),
			get_var_double('dades_inscripcio.mitjana')+get_var_double('dades_inscripcio.variança')) AS integer);
	END IF;
	RETURN NEW;
END;
$generarInscripcio$ LANGUAGE plpgsql;

-- Disparador que crida a la funció generarProva quan s'afegeix o es modifica una classificació
CREATE TRIGGER generarProva BEFORE INSERT OR UPDATE ON prova
    FOR EACH ROW EXECUTE PROCEDURE generarProva();

-- Disparador que crida a la funció generarInscripcio quan s'afegeix una prova
CREATE TRIGGER generarInscripcio AFTER INSERT ON prova
    FOR EACH ROW EXECUTE PROCEDURE generarInscripcio();

-- Crea diverses proves que les afegeix a la taula PROVA
INSERT INTO prova (descripcio)
SELECT 'Prova ' || num AS descripcio
FROM generate_series(1, get_aleatori_int('dades_prova.min_total', 'dades_prova.max_total')) AS num;

-- Eliminem el disparador i la funció de generació per a que no estiguin operatives en producció
DROP TRIGGER generarProva ON prova;
DROP FUNCTION IF EXISTS generarProva();

-- Eliminem el disparador i la funció de generació per a que no estiguin operatives en producció
DROP TRIGGER generarInscripcio ON prova;
DROP FUNCTION IF EXISTS generarInscripcio();
DROP SEQUENCE seqIncripcio;

-- Funció que retorna una opció de tipus test de forma aleatòria
CREATE OR REPLACE FUNCTION generarOpcioCorrecte() RETURNS character(1) AS $$
DECLARE p double precision;
BEGIN
	-- igualtat de probabilitats en triar una opció o una altra
	p := random() * 4;
	RETURN CASE
		WHEN p < 1 THEN 'A'
		WHEN p < 2 THEN 'B'
		WHEN p < 3 THEN 'C'
		ELSE 'D' END;
END;
$$ LANGUAGE plpgsql;

-- Funció que genera rondes de forma aleatòria
CREATE OR REPLACE FUNCTION generarRonda() RETURNS TRIGGER AS $generarRonda$
DECLARE isRondaExist boolean;
BEGIN
	-- nombre de preguntes entre 6 i 15 per a cada ronda
	NEW.nombrepreguntes := get_aleatori_int('ronda.min_preguntes', 'ronda.max_preguntes');
	-- averiguem si existeix alguna ronda en la mateixa prova (cas de ronda final amb una eliminatòria)
	isRondaExist := (SELECT EXISTS (SELECT * FROM ronda
			WHERE idprova = NEW.idprova AND tipusronda != NEW.tipusronda));
	IF isRondaExist THEN -- obtenim data fi realització última ronda de la prova
		NEW.datainicirealitzacio := (SELECT max(datafirealitzacio) FROM ronda WHERE idprova = NEW.idprova);
	ELSE -- obtenim data fi d'inscripció
		NEW.datainicirealitzacio := (SELECT datafiinscripcio FROM prova WHERE idprova = NEW.idprova);
	END IF;
	NEW.datainicirealitzacio := NEW.datainicirealitzacio + 1
		+ get_aleatori_int('dades_prova.dies_min_espera_realitzacio', 'dades_prova.dies_max_espera_realitzacio');
	-- data fi de realització entre 1 setmana i 1 mes
	NEW.datafirealitzacio := NEW.datainicirealitzacio + 1
		+ get_aleatori_int('dades_prova.dies_min_durada_realitzacio', 'dades_prova.dies_max_durada_realitzacio');
	-- generem les dades d'auditoria
	NEW.dataAlta = (SELECT dataAlta FROM prova WHERE idprova = NEW.idprova);
	NEW.usuariAlta = (SELECT usuariAlta FROM prova WHERE idprova = NEW.idprova);
	NEW.dataMod = (SELECT dataMod FROM prova WHERE idprova = NEW.idprova);
	NEW.usuariMod = (SELECT usuariMod FROM prova WHERE idprova = NEW.idprova);
	RETURN NEW;
END;
$generarRonda$ LANGUAGE plpgsql;

-- Funció que genera les respostes correctes de forma aleatòria
CREATE OR REPLACE FUNCTION generarRespostesCorrectes() RETURNS TRIGGER AS $generarRespostesCorrectes$
BEGIN
	-- Afegeix les respostes correctes a les preguntes de la ronda en la taula RESPOSTACORRECTE
	INSERT INTO respostacorrecta (idprova, tipusronda, numpregunta, opciocorrecta,
		dataAlta, usuariAlta, dataMod, usuariMod)
	SELECT NEW.idprova AS idprova, NEW.tipusronda AS tipusronda,
		num AS numpregunta, generarOpcioCorrecte() AS opciocorrecta,
		NEW.dataAlta, NEW.usuariAlta, NEW.dataMod, NEW.usuariMod
	FROM generate_series(1, NEW.nombrepreguntes) AS num;
	RETURN NEW;
END;
$generarRespostesCorrectes$ LANGUAGE plpgsql;

-- Disparador que crida a la funció generarRonda quan s'afegeix o es modifica una ronda
CREATE TRIGGER generarRonda BEFORE INSERT ON ronda
    FOR EACH ROW EXECUTE PROCEDURE generarRonda();

-- Disparador que crida a la funció generarRespostesCorrectes quan s'afegeix o es modifica una ronda
CREATE TRIGGER generarRespostesCorrectes AFTER INSERT ON ronda
    FOR EACH ROW EXECUTE PROCEDURE generarRespostesCorrectes();

-- Crea rondes eliminatòries per a les proves de més de 10 places
INSERT INTO ronda (idprova, tipusronda)
SELECT idprova, 'E' AS tipusronda FROM prova WHERE nombreplaces > get_var_int('prova.max_participants_final');

-- Crea rondes finals per a cada prova
INSERT INTO ronda (idprova, tipusronda)
SELECT idprova, 'F' AS tipusronda FROM prova;

-- Eliminem el disparador i la funció de generació per a que no estiguin operatives en producció
DROP TRIGGER generarRonda ON ronda;
DROP TRIGGER generarRespostesCorrectes ON ronda;
DROP FUNCTION IF EXISTS generarRonda();
DROP FUNCTION IF EXISTS generarRespostesCorrectes();

-- Funció que genera una participació de forma aleatòria
CREATE OR REPLACE FUNCTION generarParticipacio() RETURNS TRIGGER AS $generarParticipacio$
DECLARE p double precision := random();
BEGIN
	-- hi ha un 20% de possibilitats que un inscrit a la ronda no participi
	IF p > get_var_double('dades_participacio.probabilitat') THEN
		RETURN NULL;
	END IF;
	-- assigna el dia de la participació a la eliminatòria de forma aleatòria
	NEW.dataparticipacio = (SELECT dataIniciRealitzacio
		+ CAST(p*(dataFiRealitzacio-dataIniciRealitzacio) AS INTEGER)
		FROM ronda WHERE idprova = NEW.idprova AND tipusronda = NEW.tipusronda);
	RETURN NEW;
END;
$generarParticipacio$ LANGUAGE plpgsql;

-- Disparador que crida a la funció generarParticipacio quan s'afegeix o es modifica una ronda
CREATE TRIGGER generarParticipacio BEFORE INSERT ON participacio
    FOR EACH ROW EXECUTE PROCEDURE generarParticipacio();

-- Funció que retorna una opció de tipus test de forma aleatòria
CREATE OR REPLACE FUNCTION generarOpcioEstudiant(correcte character(1))
	RETURNS character(1) AS $$
DECLARE
	p_encert double precision := get_var_double('dades_resposta.prob_encert');
	p_error double precision := get_var_double('dades_resposta.prob_error');
	p_enblanc double precision := get_var_double('dades_resposta.prob_enblanc');
	-- si el valor aleatòri és troba en la distribució de la probabilitat d'una resposta retorna aquesta
	p1 double precision := random() * (p_encert + p_error + p_enblanc);
	-- igualtat de probabilitats en triar una opció
	p2 double precision := random() * 3;
BEGIN
	RETURN CASE
		WHEN p1 < p_encert THEN correcte -- retorna la resposta correcte
		WHEN p1 < p_encert + p_error THEN -- retorna una resposa incorrecte
			(CASE
				WHEN correcte = 'A' THEN (CASE WHEN p2<1 THEN 'B' WHEN p2<2 THEN 'C' ELSE 'D' END)
				WHEN correcte = 'B' THEN (CASE WHEN p2<1 THEN 'A' WHEN p2<2 THEN 'C' ELSE 'D' END)
				WHEN correcte = 'C' THEN (CASE WHEN p2<1 THEN 'A' WHEN p2<2 THEN 'B' ELSE 'D' END)
				ELSE (CASE WHEN p2<1 THEN 'A' WHEN p2<2 THEN 'B' ELSE 'C' END)
			END)
		ELSE 'E' END; -- retorna una resposta en blanc
END;
$$ LANGUAGE plpgsql;

-- Funció que genera respostes de l'estudiant a una participació de forma aleatòria
CREATE OR REPLACE FUNCTION generarRespostaEstudiant() RETURNS TRIGGER AS $generarRespostaEstudiant$
DECLARE numPreguntes integer;
BEGIN
	numPreguntes := (SELECT nombrepreguntes FROM ronda
			WHERE idprova = NEW.idprova AND tipusronda = NEW.tipusronda);
	-- Afegeix respostes a les preguntes de la ronda en la taula RESPOSTAESTUDIANT
	INSERT INTO respostaestudiant (idprova, tipusronda, idestudiant, numpregunta, opciomarcada)
	SELECT idprova, tipusronda, NEW.idestudiant AS idestudiant,
		numpregunta, generarOpcioEstudiant(opciocorrecta) AS opciomarcada
	FROM respostacorrecta WHERE idprova = NEW.idprova AND tipusronda = NEW.tipusronda;
	RETURN NEW;
END;
$generarRespostaEstudiant$ LANGUAGE plpgsql;

-- Disparador que crida a la funció generarRespostaEstudiant quan s'afegeix o es modifica una ronda
CREATE TRIGGER generarRespostaEstudiant AFTER INSERT ON participacio
    FOR EACH ROW EXECUTE PROCEDURE generarRespostaEstudiant();

-- Afegeix una participació per a cada inscripció a cada eliminatòria a la taula PARTICIPACIO
-- sempre que el número d'inscripcions superi el número màxim de participants a la final
INSERT INTO participacio (idprova, tipusronda, idestudiant)
-- Eliminatòries de gran nombre d'inscripcions
SELECT i.idprova, r.tipusronda, i.idestudiant FROM inscripcio i
LEFT JOIN ronda AS r ON r.idprova = i.idprova AND r.tipusronda = 'E'
LEFT JOIN (SELECT idprova, count(*) AS num
	FROM inscripcio GROUP BY idprova) AS p
	ON p.idprova = i.idprova
WHERE datainicirealitzacio <= current_date
	AND p.num > get_var_int('prova.max_participants_final');

-- Funció que obté els resultats de les participacions al tancar les rondes
CREATE OR REPLACE FUNCTION calcularResultats() RETURNS TRIGGER AS $calcularResultats$
BEGIN
	NEW.encerts := (SELECT count(*) FROM respostaestudiant AS re
			LEFT JOIN respostacorrecta AS rc
				ON rc.idprova = re.idprova
				AND rc.tipusronda = re.tipusronda
				AND rc.numPregunta = re.numPregunta
			WHERE re.idprova = NEW.idprova
				AND re.tipusronda = NEW.tipusronda
				AND re.idestudiant = NEW.idestudiant
				AND re.opciomarcada = rc.opciocorrecta);
	NEW.enblanc := (SELECT count(*) FROM respostaestudiant
			WHERE idprova = NEW.idprova
				AND tipusronda = NEW.tipusronda
				AND idestudiant = NEW.idestudiant
				AND opciomarcada = 'E'); -- opció en blanc
	NEW.errors := (SELECT nombrepreguntes FROM ronda
			WHERE idprova = NEW.idprova
				AND tipusronda = NEW.tipusronda)
			- NEW.encerts - NEW.enblanc;
	NEW.puntuacio = NEW.encerts * get_var_double('classificacio.puntuacio_encerts')
		+ NEW.errors * get_var_double('classificacio.puntuacio_errors')
		+ NEW.enblanc * get_var_double('classificacio.puntuacio_enblanc');
	RETURN NEW;
END;
$calcularResultats$ LANGUAGE plpgsql;

-- Disparador que crida a la funció anterior quan s'afegeix o es modifica una classificació
CREATE TRIGGER calcularResultats BEFORE INSERT OR UPDATE ON classificacio
    FOR EACH ROW EXECUTE PROCEDURE calcularResultats();

-- Funció que calcula les classificacions dels estudiants a cadascuna de les rondes
CREATE OR REPLACE FUNCTION calcularClassificacions() RETURNS void AS $$
BEGIN
	-- Esborrem primer les dades per a poder invocar el mètode varies vegades
	TRUNCATE TABLE classificacio CASCADE;

	-- Afegeix resultats per a cada participació al final de cada ronda a la taula CLASSIFICACIO
	INSERT INTO classificacio (idprova, tipusronda, idestudiant, posicio)
	SELECT p.idprova, p.tipusronda, p.idestudiant, 0 -- Pendent de calcular als passos següents
	FROM participacio p
	LEFT JOIN ronda r ON r.idprova = p.idprova AND r.tipusronda = p.tipusronda
	WHERE r.datafirealitzacio < current_date;

	-- S'emmagatzema l'ordre en el que es troben els estudiants segons:
	-- idProva, tipusRonda, puntuacio DESC, dataparticipacio
	CREATE SEQUENCE podium START 1;
	UPDATE classificacio c SET posicio = o.ordre
	FROM (SELECT *, nextval('podium') AS ordre
		FROM (SELECT c.idprova, c.tipusronda, c.idestudiant, c.puntuacio, p.dataparticipacio
			FROM classificacio AS c
			LEFT JOIN participacio AS p
				ON p.idprova = c.idprova
				AND p.tipusronda = c.tipusronda
				AND p.idestudiant = c.idestudiant
			ORDER BY c.idprova, c.tipusronda, c.puntuacio DESC, p.dataparticipacio) AS o
		) AS o
	WHERE c.idprova = o.idprova AND c.tipusronda = o.tipusronda AND c.idestudiant = o.idestudiant;
	DROP SEQUENCE podium;

	-- L'ordre establert anteriorment s'inicialitza a 1 per a cada ronda
	UPDATE classificacio c SET posicio = posicio - g.minim + 1
	FROM (SELECT idprova, tipusronda, min(posicio) AS minim FROM classificacio GROUP BY idprova, tipusronda) AS g
	WHERE c.idprova = g.idprova AND c.tipusronda = g.tipusronda;
END;
$$ LANGUAGE plpgsql;

-- Calculem les classificacions de les rondes eliminatòries
SELECT calcularClassificacions();

-- Afegeix una participació per a cada final a la taula PARTICIPACIO
INSERT INTO participacio (idprova, tipusronda, idestudiant)
-- Finals sense eliminatòria per nombre d'inscripcions
SELECT i.idprova, r.tipusronda, i.idestudiant
FROM inscripcio i
LEFT JOIN ronda AS r ON r.idprova = i.idprova AND r.tipusronda = 'F'
LEFT JOIN (SELECT idprova, count(*) AS num
	FROM inscripcio GROUP BY idprova) AS p
	ON p.idprova = i.idprova
WHERE datainicirealitzacio <= current_date
	AND p.num <= get_var_int('prova.max_participants_final')
	AND p.num >= get_var_int('prova.min_participants')
UNION
-- Finals amb estudiants classificats en l'eliminatòria
SELECT c.idprova, r.tipusronda, c.idestudiant
FROM classificacio c
LEFT JOIN ronda AS r ON r.idprova = c.idprova AND r.tipusronda = 'F'
WHERE c.tipusronda = 'E' AND datainicirealitzacio <= current_date
	AND c.posicio <= get_var_int('prova.max_participants_final');

-- Calculem les classificacions de TOTES les rondes, tant finals com eliminatòries (de nou, no passa res)
SELECT calcularClassificacions();

-- Eliminem el disparador i la funció de generació per a que no estiguin operatives en producció
DROP TRIGGER generarParticipacio ON participacio;
DROP TRIGGER generarRespostaEstudiant ON participacio;
DROP FUNCTION IF EXISTS generarParticipacio();
DROP FUNCTION IF EXISTS generarRespostaEstudiant();
DROP FUNCTION IF EXISTS generarOpcioCorrecte();
DROP FUNCTION IF EXISTS generarOpcioEstudiant(character(1));

-- Eliminem el disparador i la funció de generació per a que no estiguin operatives en producció
DROP TRIGGER calcularResultats ON classificacio;
DROP FUNCTION IF EXISTS calcularResultats();
DROP FUNCTION IF EXISTS calcularClassificacions();
/*
-- Eliminem les funcions de generació aleatòria per a que no estiguin operatives en producció
DROP FUNCTION IF EXISTS get_aleatori_int(text, text);
DROP FUNCTION IF EXISTS get_aleatori(integer, integer);
DROP FUNCTION IF EXISTS get_aleatori_var_dif(text, integer, integer);
DROP FUNCTION IF EXISTS get_aleatori_double(text, text);
DROP FUNCTION IF EXISTS get_aleatori(double precision, double precision);
*/

-- Definim els estats de cadascuna de les rondes segons la seva casuística

-- Estat prova amb la inscripció fora de període
UPDATE prova SET estat = get_var('prova.estat_fora_periode')
WHERE current_date < dataIniInscripcio;

-- Estat prova que admet inscripcions (dins període i no assolida capacitat màxima)
UPDATE prova AS p SET estat = get_var('prova.estat_inscripcio')
FROM (SELECT idprova, count(*) AS num FROM inscripcio GROUP BY idprova) AS i
WHERE current_date >= dataIniInscripcio AND current_date <= dataFiInscripcio
	AND p.idprova = i.idprova AND i.num < p.nombreplaces;

-- Estat prova que no admet més inscripcions (dins període i assolida capacitat màxima)
UPDATE prova AS p SET estat = get_var('prova.estat_ple')
FROM (SELECT idprova, count(*) AS num FROM inscripcio GROUP BY idprova) AS i
WHERE current_date >= dataIniInscripcio AND current_date <= dataFiInscripcio
	AND p.idprova = i.idprova AND i.num >= p.nombreplaces;

-- Estat prova amb inscripcions insuficients i ha acabat llur període d'inscripció
UPDATE prova AS p SET estat = get_var('prova.estat_deserta')
FROM (SELECT idprova, count(*) AS num FROM inscripcio GROUP BY idprova) AS i
WHERE p.idprova = i.idprova
	AND i.num < get_var_int('prova.min_participants')
	AND current_date > p.dataFiInscripcio;

-- Estat prova en que els estudiants esperen competir a la eliminatòria
UPDATE prova AS p SET estat = get_var('prova.estat_inscripcio_tancada')
FROM (SELECT p.idprova FROM prova AS p
		LEFT JOIN (SELECT idprova, count(*) AS num
			FROM inscripcio GROUP BY idprova) AS i
		ON p.idprova = i.idprova
		LEFT JOIN ronda re ON re.idprova = p.idprova AND re.tipusronda = 'E'
	WHERE i.num > get_var_int('prova.max_participants_final')
		AND p.dataFiInscripcio < current_date
		AND re.dataIniciRealitzacio > current_date) AS tmp
WHERE p.idprova = tmp.idprova;

-- Estat prova en que els estudiants poden competir a la eliminatòria
UPDATE prova AS p SET estat = get_var('prova.estat_eliminatoria_competicio')
FROM (SELECT p.idprova FROM prova AS p
		LEFT JOIN (SELECT idprova, count(*) AS num
			FROM inscripcio GROUP BY idprova) AS i
		ON p.idprova = i.idprova
		LEFT JOIN ronda re ON re.idprova = p.idprova AND re.tipusronda = 'E'
	WHERE i.num > get_var_int('prova.max_participants_final')
		AND re.dataIniciRealitzacio <= current_date
		AND re.dataFiRealitzacio >= current_date) AS tmp
WHERE p.idprova = tmp.idprova;

-- Estat prova després de tancar competició de la ronda eliminatòria
UPDATE prova AS p SET estat = get_var('prova.estat_eliminatoria_tancada')
-- Finals sense eliminatòria per nombre d'inscripcions
FROM (SELECT p.idprova FROM prova p
	LEFT JOIN (SELECT idprova, count(*) AS num
		FROM inscripcio GROUP BY idprova) AS i
		ON i.idprova = p.idprova
	LEFT JOIN ronda AS rf ON rf.idprova = i.idprova AND rf.tipusronda = 'F'
	WHERE p.datafiinscripcio < current_date
		AND rf.datainicirealitzacio > current_date
		AND i.num <= get_var_int('prova.max_participants_final')
		AND i.num >= get_var_int('prova.min_participants')
UNION
-- Finals amb eliminatòria tancada
SELECT p.idprova FROM prova p
	LEFT JOIN (SELECT idprova, count(*) AS num
		FROM inscripcio GROUP BY idprova) AS i
		ON i.idprova = p.idprova
	LEFT JOIN ronda AS rf ON rf.idprova = i.idprova AND rf.tipusronda = 'F'
	LEFT JOIN ronda AS re ON re.idprova = i.idprova AND re.tipusronda = 'E'
	WHERE re.datafirealitzacio < current_date
		AND rf.datainicirealitzacio > current_date
		AND i.num > get_var_int('prova.max_participants_final')) AS tmp
WHERE p.idprova = tmp.idprova;

-- Estat prova en que els estudiants poden competir en la final
UPDATE prova AS p SET estat = get_var('prova.estat_final_competicio')
-- Finals sense eliminatòria per nombre d'inscripcions
FROM (SELECT p.idprova FROM prova p
	LEFT JOIN (SELECT idprova, count(*) AS num
		FROM inscripcio GROUP BY idprova) AS i
		ON i.idprova = p.idprova
	LEFT JOIN ronda AS rf ON rf.idprova = i.idprova AND rf.tipusronda = 'F'
	WHERE rf.datainicirealitzacio <= current_date
		AND rf.datafirealitzacio >= current_date
		AND i.num <= get_var_int('prova.max_participants')
		AND i.num >= get_var_int('prova.max_participants_final')
UNION
-- Finals amb eliminatòria tancada
SELECT p.idprova FROM prova p
	LEFT JOIN (SELECT idprova, count(*) AS num
		FROM inscripcio GROUP BY idprova) AS i
		ON i.idprova = p.idprova
	LEFT JOIN ronda AS rf ON rf.idprova = i.idprova AND rf.tipusronda = 'F'
	WHERE rf.datainicirealitzacio <= current_date
		AND rf.datafirealitzacio >= current_date
		AND i.num <= get_var_int('prova.max_participants_final')
		AND i.num >= get_var_int('prova.min_participants')) AS tmp
WHERE p.idprova = tmp.idprova;

-- Estat prova després de tancar competició de la ronda final
UPDATE prova AS p SET estat = get_var('prova.estat_final_tancada')
-- Finals sense eliminatòria per nombre d'inscripcions
FROM (SELECT p.idprova FROM prova p
	LEFT JOIN (SELECT idprova, count(*) AS num
		FROM inscripcio GROUP BY idprova) AS i
		ON i.idprova = p.idprova
	LEFT JOIN ronda AS rf ON rf.idprova = i.idprova AND rf.tipusronda = 'F'
	WHERE rf.datafirealitzacio < current_date
		AND i.num >= get_var_int('prova.min_participants')) AS tmp
WHERE p.idprova = tmp.idprova
	AND EXISTS (SELECT * FROM classificacio c
		WHERE c.idprova = tmp.idprova AND c.tipusronda = 'F');

-- Estat prova final tancada sense participacions
UPDATE prova AS p SET estat = get_var('prova.estat_deserta')
FROM (SELECT p.idprova FROM prova p
	LEFT JOIN (SELECT idprova, count(*) AS num
		FROM inscripcio GROUP BY idprova) AS i
		ON i.idprova = p.idprova
	LEFT JOIN ronda AS rf ON rf.idprova = i.idprova AND rf.tipusronda = 'F'
	WHERE rf.datafirealitzacio < current_date
		AND i.num >= get_var_int('prova.min_participants')) AS tmp
WHERE p.idprova = tmp.idprova
	AND NOT EXISTS (SELECT * FROM classificacio c
		WHERE c.idprova = tmp.idprova AND c.tipusronda = 'F');

--/*
-- Mostra tota la informació rellevant de les proves
SELECT p.idprova, p.estat, p.nombreplaces, i.num AS "inscripcions",
	pe.num AS "Particip. Elim.", ce.num AS "Class. Elim.",
	pf.num AS "Particip. Final", cf.num AS "Class. Final",
	p.dataIniInscripcio AS "Inici inscripció", p.dataFiInscripcio AS "Fi inscripció",
	re.dataIniciRealitzacio AS "Eliminatoria inici", re.dataFiRealitzacio AS "Eliminatoria fi",
	rf.dataIniciRealitzacio AS "Ronda Final inici", rf.dataFiRealitzacio AS "Ronda Final fi"
FROM prova AS p
	LEFT JOIN (SELECT idprova, count(*) AS num
		FROM inscripcio GROUP BY idprova) AS i
	ON p.idprova = i.idprova
	LEFT JOIN ronda re ON re.idprova = p.idprova AND re.tipusronda = 'E'
	LEFT JOIN (SELECT idprova, tipusronda, count(*) AS num
		FROM participacio GROUP BY idprova, tipusronda) AS pe
	ON pe.idprova = re.idprova AND pe.tipusronda = re.tipusronda
	LEFT JOIN (SELECT idprova, tipusronda, count(*) AS num
		FROM classificacio GROUP BY idprova, tipusronda) AS ce
	ON ce.idprova = re.idprova AND ce.tipusronda = re.tipusronda
	LEFT JOIN ronda rf ON rf.idprova = p.idprova AND rf.tipusronda = 'F'
	LEFT JOIN (SELECT idprova, tipusronda, count(*) AS num
		FROM participacio GROUP BY idprova, tipusronda) AS pf
	ON pf.idprova = rf.idprova AND pf.tipusronda = rf.tipusronda
	LEFT JOIN (SELECT idprova, tipusronda, count(*) AS num
		FROM classificacio GROUP BY idprova, tipusronda) AS cf
	ON cf.idprova = rf.idprova AND cf.tipusronda = rf.tipusronda
ORDER BY estat, idprova;
--*/
